package com.atguigu.edu.realtime.util;

import org.apache.commons.beanutils.BeanUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PhoenixUtil {

    public static void executeSql(String sql) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {

            conn = DruidDSUtil.getConnection();
            //获取数据库操作对象
            ps = conn.prepareStatement(sql);
            //执行SQL语句
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放资源
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static String getBaseDicDDL(){
        String createBaseDicSQL="   CREATE   TABLE base_dic (\n" +
                "               dic_code STRING,\n" +
                "               dic_name STRING,\n" +
                "              PRIMARY KEY (dic_code) NOT ENFORCED\n" +
                "             ) WITH (\n" +
                "               'connector' = 'jdbc',\n" +
                "               'url' = 'jdbc:phoenix:hadoop102:2181',\n"+
                "              'table-name' = 'EDU.DIM_BASE_DIC',\n"+
                "               'lookup.cache.max-rows' ='1000',\n" +
                "               'lookup.cache.ttl' ='60s'\n" +
                "             ) ";
        return createBaseDicSQL;
    }

    public static  <T> List<T> queryList(String sql,Class<T> clazz) {

        List<T> resultList=new ArrayList<>();

        Connection conn = null;
        PreparedStatement ps = null;
        try {

            conn = DruidDSUtil.getConnection();
            //获取数据库操作对象
            ps = conn.prepareStatement(sql);
            //执行SQL语句
            ResultSet resultSet = ps.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData(); //列名集合
            //循环结果集，提取一行行的值
            while(resultSet.next()){
                T obj = clazz.newInstance();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String columnName =  metaData.getColumnName(i).toLowerCase()  ;  //取得列名  //大写-->小写
                    Object columnValue = resultSet.getObject(i); //取得列值
                    BeanUtils.setProperty(obj,columnName,columnValue);
                }
                resultList.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放资源
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return resultList;

    }

    public static  <T>  T queryOne(String sql,Class<T> clazz) {
        List<T> queryList = queryList(sql, clazz);

        if(queryList!=null&&queryList.size()>0){
            return queryList.get(0);
        }else{
            return null;
        }
    }
}